﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using UniversityManagementSystem.DAL;

namespace UniversityManagementSystem.Gateway
{
    public class ClassScheduleGateway : GatewayConnection
    {
        private string commandString = null;
        private SqlDataReader getData = null;

        public bool SaveSchedule(ClassSchedule aClassSchedule)
        {
            bool saveSchedule = false;
            int assigned = 0;
            int update = 0;
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("INSERT INTO t_class_schedule(building_name,room_no,class_day,class_start,class_end,assigned_course)VALUES(@buildingName,@roomNo,@classDay,@classStart,@classEnd,@assignedCourse)");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@buildingName", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@buildingName"].Value = aClassSchedule.BuildingName;
                GetSqlCommand.Parameters.Add("@roomNo", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@roomNo"].Value = aClassSchedule.RoomNo;
                GetSqlCommand.Parameters.Add("@classDay", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@classDay"].Value = aClassSchedule.ClassDay;
                GetSqlCommand.Parameters.Add("@classStart", SqlDbType.Time);
                GetSqlCommand.Parameters["@classStart"].Value = aClassSchedule.ClassStarTime;
                GetSqlCommand.Parameters.Add("@classEnd", SqlDbType.Time);
                GetSqlCommand.Parameters["@classEnd"].Value = aClassSchedule.ClassEndTime;
                GetSqlCommand.Parameters.Add("@assignedCourse", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@assignedCourse"].Value = aClassSchedule.CourseName;
                assigned = GetSqlCommand.ExecuteNonQuery();

                commandString = string.Format("UPDATE t_Course_schedule SET schedeuled = @schedeuled WHERE course_title = @assignedCourse");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@assignedCourse", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@assignedCourse"].Value = aClassSchedule.CourseName;
                GetSqlCommand.Parameters.Add("@schedeuled", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@schedeuled"].Value = "Y";
                update = GetSqlCommand.ExecuteNonQuery();
                GetSqlConn.Close();
                if ((assigned > 0) && (update > 0))
                {
                    saveSchedule = true;
                }

            }
            catch (Exception exception)
            {

                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return saveSchedule;
        }

        public List<ClassSchedule> GetClassSchedule(ClassSchedule aClassSchedule)
        {
            List<ClassSchedule> getClassSchedule = new List<ClassSchedule>();
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_class_schedule WHERE building_name = @buildingName AND room_no = @roomNo AND class_day = @classDay AND (class_start between @classStart AND @classEnd OR class_end between @classStart AND @classEnd OR class_start <= @classStart OR class_end >= @classEnd)");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@buildingName", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@buildingName"].Value = aClassSchedule.BuildingName;
                GetSqlCommand.Parameters.Add("@roomNo", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@roomNo"].Value = aClassSchedule.RoomNo;
                GetSqlCommand.Parameters.Add("@classDay", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@classDay"].Value = aClassSchedule.ClassDay;
                GetSqlCommand.Parameters.Add("@classStart", SqlDbType.Time);
                GetSqlCommand.Parameters["@classStart"].Value = aClassSchedule.ClassStarTime;
                GetSqlCommand.Parameters.Add("@classEnd", SqlDbType.Time);
                GetSqlCommand.Parameters["@classEnd"].Value = aClassSchedule.ClassEndTime;
                getData = GetSqlCommand.ExecuteReader();
                while (getData.Read())
                {
                    ClassSchedule getSchedule = new ClassSchedule();
                    getSchedule.BuildingName = getData["building_name"].ToString();
                    getSchedule.RoomNo = getData["room_no"].ToString();
                    getSchedule.ClassDay = getData["class_day"].ToString();
                    getSchedule.ClassStarTime = TimeSpan.Parse(getData["class_start"].ToString());
                    getSchedule.ClassEndTime = TimeSpan.Parse(getData["class_end"].ToString());
                    getSchedule.CourseName = getData["assigned_course"].ToString();
                    getClassSchedule.Add(getSchedule);
                }
                GetSqlConn.Close();
            }

            catch (Exception exception)
            {

                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return getClassSchedule;
        }

        public List<ClassSchedule> GetClassScheduleTeacher(ClassSchedule aClassSchedule)
        {
            List<ClassSchedule> getClassSchedule = new List<ClassSchedule>();
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_class_schedule WHERE assigned_course IN (SELECT course_title FROM t_Course_schedule WHERE assigned_teacher = @assignedTeacher) AND (class_start between @classStart AND @classEnd OR class_end between @classStart AND @classEnd OR class_start <= @classStart OR class_end >= @classEnd)");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@assignedTeacher", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@assignedTeacher"].Value = aClassSchedule.CourseTeacher;
                GetSqlCommand.Parameters.Add("@classStart", SqlDbType.Time);
                GetSqlCommand.Parameters["@classStart"].Value = aClassSchedule.ClassStarTime;
                GetSqlCommand.Parameters.Add("@classEnd", SqlDbType.Time);
                GetSqlCommand.Parameters["@classEnd"].Value = aClassSchedule.ClassEndTime;
                getData = GetSqlCommand.ExecuteReader();
                while (getData.Read())
                {
                    ClassSchedule getSchedule = new ClassSchedule();
                    getSchedule.BuildingName = getData["building_name"].ToString();
                    getSchedule.RoomNo = getData["room_no"].ToString();
                    getSchedule.ClassDay = getData["class_day"].ToString();
                    getSchedule.ClassStarTime = TimeSpan.Parse(getData["class_start"].ToString());
                    getSchedule.ClassEndTime = TimeSpan.Parse(getData["class_end"].ToString());
                    getSchedule.CourseName = getData["assigned_course"].ToString();
                    getClassSchedule.Add(getSchedule);
                }
                GetSqlConn.Close();
            }

            catch (Exception exception)
            {

                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return getClassSchedule;
        }
    }
}